This article is about to create a log file in MS Access using VBA code. This log file will keep track all the record who visits to database. This will store information of time period, system name and object type. Logging gives information regarding to take feedback regarding your database. This utility will store the information regarding time and system name who visit the database. We can make changes on this utility according to requirement.

To implement this utility firstly we have to create the table which stores the logging information as shown in Fig 1.1.

Then we have to create the Form with following controls as shown in Fig 1.2. On open and on close event of this form we have to write function using VBA code.

After implementing the on open and on close event, we have to test the implementation. Now open the Form and close it. To check the time period and system name we have to open the log table. This table will store the information regarding time period as shown in Fig 1.3.

We can review this information in report also for documentation as shown in Fig 1.4.

VBA CODE :

Option Compare Database
Option Explicit
Private Const logBox As Boolean = True
' get computer name
Private Declare Function apiGetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Function ComputerName() As String
Dim sysName As String
Dim sLen As Long
sLen = 10&
sysName = String$(sLen, vbNullChar)
If apiGetComputerName(sysName, sLen) = 0& Then
ComputerName = "Unknown"
Else
ComputerName = Left$(sysName, sLen)
End If
End Function

Public Function OpenLog(obj As Object) As Long
Dim SDoc As String
Dim rs As DAO.Recordset
If logBox Then
SDoc = obj.Name
Set rs = DBEngine(0)(0).OpenRecordset("LOG_TABLE", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!OpenDateTime = Now()
rs!CloseDateTime = Null
rs!DocName = SDoc
rs!ComputerName = ComputerName()
rs.Update
rs.Bookmark = rs.LastModified
OpenLog = rs!LogDocID
rs.Close
End If
End Function

Public Function CloseLog(obj As Object) As Long
Dim rs As DAO.Recordset
Dim sqlString As String
Dim SDoc As String
Dim sysString As String
If logBox Then
SDoc = obj.Name
sysString = ComputerName()
sqlString = "SELECT LOG_TABLE.* FROM LOG_TABLE WHERE ((LOG_TABLE.DocName = """ & SDoc & _ """) AND (LOG_TABLE.ComputerName = """ & sysString & _ """) AND (LOG_TABLE.CloseDateTime Is Null) AND (LOG_TABLE.OpenDateTime <= Now())) ORDER BY LOG_TABLE.OpenDateTime, LOG_TABLE.LogDocID;"
Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
If rs.RecordCount > 0& Then
rs.Edit
rs!CloseDateTime = Now()
rs.Update
Else
rs.AddNew
rs!OpenDateTime = Null
rs!CloseDateTime = Now()
rs!DocName = SDoc
rs!ComputerName = sysString
rs.Update
End If
rs.Bookmark = rs.LastModified
CloseLog = rs!LogDocID
rs.Close
End If
End Function